package com.oocl.aeat.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import com.oocl.aeat.dao.VerifyDao;
import com.oocl.aeat.pojo.Verify;
import com.oocl.aeat.util.DBHelper;
import com.oocl.aeat.util.DataSourceUtils;

public class VerifyDaoImpl implements VerifyDao {

	@Override
	public long getCurrVal() {
		String sql = "SELECT verifySeq.currval FROM DUAL ";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		ResultSet rst = null;
		long result=-1;
		try {
			pst = conn.prepareStatement(sql);
			rst = pst.executeQuery();
			while(rst.next()){
				result = rst.getLong(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, rst, null);
		}
		return result;
	}
	
	@Override
	public int addVerify(Verify verify) {
		String sql = "insert into verify(id, name, store_name, mid, pwd, idcard,img,address,submit_time, verify_time,state,block,msg)"
				+ " values(verifySeq.nextVal,?,?,?,?,?,?,?,?,?,?,?,?)";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, verify.getName());
			pst.setString(2, verify.getStoreName());
			pst.setString(3, verify.getMid());
			pst.setString(4, verify.getPwd());
			pst.setString(5, verify.getIdcard());
			pst.setString(6, verify.getImg());
			pst.setString(7, verify.getAddress());
			pst.setLong(8, (verify.getSubmitTime()==null) ? -1 : verify.getSubmitTime());
			pst.setLong(9, (verify.getVerifyTime()==null) ? -1 : verify.getVerifyTime());
			pst.setInt(10, verify.getState());
			pst.setInt(11, verify.getBlock());
			pst.setString(12, verify.getMsg());
			
			 return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		}finally {
			DBHelper.close(pst, null, null);
		}
		return -1;
	}

	@Override
	public int deleteVerify(Integer id) {
		String sql = "delete from verify where id = ? ";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, id);
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, null);
		}
		return -1;
	}

	@Override
	public int updateVerify(Verify verify) {
		String sql = "update verify set name=?,store_name=?, mid=?, pwd=?, idcard=?,img=?,address=?,submit_time=?, verify_time=?,state=?,block=?,msg=? where id=? ";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setString(1, verify.getName());
			pst.setString(2, verify.getStoreName());
			pst.setString(3, verify.getMid());
			pst.setString(4, verify.getPwd());
			pst.setString(5, verify.getIdcard());
			pst.setString(6, verify.getImg());
			pst.setString(7, verify.getAddress());
			pst.setLong(8, verify.getSubmitTime());
			pst.setLong(9, verify.getVerifyTime());
			pst.setInt(10, verify.getState());
			pst.setInt(11, verify.getBlock());
			pst.setString(12, verify.getMsg());
			pst.setInt(13, verify.getId());
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, null);
		}
		return -1;
	}

	@Override
	public int modifyState(String mid, Integer state) {
		String sql = "update verify set verify_time=?, state = ? where mid = ? and state != 1";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setLong(1, new Date().getTime());
			pst.setInt(2, state);
			pst.setString(3,mid);
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, null);
		}
		return -1;
	}
	
	@Override
	public int modifyBlock(String mid, Integer block) {
		String sql = "update verify set block = ? where mid = ?";
		Connection conn = DataSourceUtils.getConn();
		PreparedStatement pst = null;
		try {
			pst = conn.prepareStatement(sql);
			pst.setInt(1, block);
			pst.setString(2,mid);
			return pst.executeUpdate();
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, null, null);
		}
		return -1;
	}
	
	@Override
	public List<Verify> queryAllVerifyLastly(Integer state) {
		Connection conn = DataSourceUtils.getConn();
		List<Verify> list = new ArrayList<Verify>();
		Verify verify = null;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t.* FROM (SELECT mid, MAX(submit_time) as LastTime FROM verify GROUP BY mid) r ")
		.append("INNER JOIN verify t ON t.mid = r.mid AND t.submit_time = r.LastTime ");
		if(state!=null){
			sb.append("where t.state = ? ");
		}

		PreparedStatement pst = null;
		ResultSet rst = null;
		try {
			pst =  conn.prepareStatement(sb.toString());
			if(state!=null){
				pst.setInt(1, state);
			}
			rst = pst.executeQuery();
			while(rst.next()){
				verify = new Verify();
				verify.setId(rst.getInt(1));
				verify.setName(rst.getString(2));
				verify.setStoreName(rst.getString(3));
				verify.setMid(rst.getString(4));
				verify.setPwd(rst.getString(5));
				verify.setIdcard(rst.getString(6));
				verify.setImg(rst.getString(7));
				verify.setAddress(rst.getString(8));
				verify.setSubmitTime(rst.getLong(9));
				verify.setVerifyTime(rst.getLong(10));
				verify.setState(rst.getInt(11));
				verify.setBlock(rst.getInt(12));
				verify.setMsg(rst.getString(13));
				list.add(verify);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, rst, null);
		}
		return list;
	}
	
	@Override
	public Verify queryVerifyLastlyByMid(String mid) {
		Connection conn = DataSourceUtils.getConn();
		List<Verify> list = new ArrayList<Verify>();
		Verify verify = null;
		StringBuilder sb = new StringBuilder();
		sb.append("SELECT t.* FROM (SELECT mid, MAX(submit_time) as LastTime FROM verify GROUP BY mid) r ")
		.append("INNER JOIN verify t ON t.mid = r.mid AND t.submit_time = r.LastTime where t.mid = ? ");

		PreparedStatement pst = null;
		ResultSet rst = null;
		try {
			pst =  conn.prepareStatement(sb.toString());
			pst.setString(1, mid);
			rst = pst.executeQuery();
			while(rst.next()){
				verify = new Verify();
				verify.setId(rst.getInt(1));
				verify.setName(rst.getString(2));
				verify.setStoreName(rst.getString(3));
				verify.setMid(rst.getString(4));
				verify.setPwd(rst.getString(5));
				verify.setIdcard(rst.getString(6));
				verify.setImg(rst.getString(7));
				verify.setAddress(rst.getString(8));
				verify.setSubmitTime(rst.getLong(9));
				verify.setVerifyTime(rst.getLong(10));
				verify.setState(rst.getInt(11));
				verify.setBlock(rst.getInt(12));
				verify.setMsg(rst.getString(13));
				list.add(verify);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			DBHelper.close(pst, rst, null);
		}
		return verify;
	}

	@Override
	public Verify queryVerifyById(Integer id) {
		Connection conn = DataSourceUtils.getConn();
		Verify verify = null;
		String sql = "select * from verify where id = ? ";
		PreparedStatement pst = null;
		ResultSet rst = null;
		try {
			pst =  conn.prepareStatement(sql);
			pst.setInt(1, id);
			rst = pst.executeQuery();
			while(rst.next()){
				verify = new Verify();
				verify.setId(rst.getInt(1));
				verify.setName(rst.getString(2));
				verify.setStoreName(rst.getString(3));
				verify.setMid(rst.getString(4));
				verify.setPwd(rst.getString(5));
				verify.setIdcard(rst.getString(6));
				verify.setImg(rst.getString(7));
				verify.setAddress(rst.getString(8));
				verify.setSubmitTime(rst.getLong(9));
				verify.setVerifyTime(rst.getLong(10));
				verify.setState(rst.getInt(11));
				verify.setBlock(rst.getInt(12));
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBHelper.close(pst, rst, null);
		}
		return verify;
	}

	@Override
	public int queryNewVerifyCount(long time) {
		Connection conn = DataSourceUtils.getConn();
		String sql = "select count(*) from verify where submit_time > ? ";
		PreparedStatement pst = null;
		ResultSet rst = null;
		int result = 0;
		try {
			pst =  conn.prepareStatement(sql);
			pst.setLong(1, time);
			rst = pst.executeQuery();
			while(rst.next()){
				result = rst.getInt(1);
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}
		finally {
			DBHelper.close(pst, rst, null);
		}
		return result;
	}
}
